﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CommonLib.DbHelper
{
    public class Table
    {
        /// <summary>
        /// 缓存帮助类
        /// </summary>
        //public FrameWork.CacheHelper Cache = FrameWork.CacheHelper.Instance;

        public IDBhelper IdBhelper
        {
            get; set;
        }

        public Table(string ConnString = "ConnectionString")
        {
            this.IdBhelper = new CommonLib.DbHelper.Factory() { Context = new CommonLib.DbHelper.DbContext() { ConnString = ConnString } }.IDBhelper;
        } 


        /// <summary>
        /// 表结构
        /// </summary>
        public Dictionary<string, Model.Table> DicTable
        {
            get
            {
                return GetTables();
            }
        }

        #region 获取数据库表字段实体

        /// <summary>
        /// 获取数据库表字段实体
        /// </summary> 
        /// <returns></returns>
        private Dictionary<string, Model.Table> GetTables()
        {

            var DicTable = new Dictionary<string, Model.Table>();
            DicTable = new Dictionary<string, Model.Table>();
            var Table = this.IdBhelper.ExecuteScalars("SELECT name FROM sys.sysobjects WHERE (type = 'U') and (name<>'sysdiagrams') ORDER BY name, crdate").Split(',');
            var sdr = this.IdBhelper.ExecuteReader(@"SELECT a.name AS 字段名, b.name AS 字段类型, c.name AS 表名, e.value AS 字段说明, f.value AS 表说明, 
                                                      a.max_length AS 字段长度, a.is_nullable AS 可空
                                                FROM sys.columns AS a LEFT OUTER JOIN
                                                        sys.types AS b ON a.user_type_id = b.user_type_id INNER JOIN
                                                        sys.objects AS c ON a.object_id = c.object_id AND c.type = 'U' LEFT OUTER JOIN
                                                        sys.syscomments AS d ON a.default_object_id = d.id LEFT OUTER JOIN
                                                        sys.extended_properties AS e ON e.major_id = c.object_id AND e.minor_id = a.column_id AND 
                                                        e.class = 1 LEFT OUTER JOIN
                                                        sys.extended_properties AS f ON f.major_id = c.object_id AND f.minor_id = 0 AND f.class = 1 and c.name <> 'sysdiagrams'
                                                ORDER BY 表名");

            var list = new List<Model.TableColumn>();

            while (sdr.Read())
            {
                list.Add(new Model.TableColumn()
                {
                    TableName = sdr["表名"].ToString(),
                    TableComments = sdr["表说明"].ToString(),
                    Name = sdr["字段名"].ToString(),
                    Comments = sdr["字段说明"].ToString(),
                    IsPrimaryKey = false,
                    IsNull = sdr["可空"].ToString() == "0" ? false : true,
                    Length = Convert.ToInt32(sdr["字段长度"].ToString()),
                    Type = sdr["字段类型"].ToString(),
                });
            }
            sdr.Close();

            foreach (var item in Table)
            {
                var identity = this.IdBhelper.ExecuteScalar(string.Format("select column_name from information_schema.columns where table_name = '{0}' and columnproperty( object_id('{0}'), column_name, 'isidentity') = 1", item));
                var primaryName = this.IdBhelper.ExecuteScalar("select column_name from information_schema.key_column_usage where(table_name = '" + item + "')");
                var table = new Model.Table() { Name = item, PrimaryName = primaryName, Comments = list.Where(ex => ex.TableName == item).FirstOrDefault().TableComments }; ;
                table.PrimaryName = primaryName;
                table.Columns = list.Where(ex => ex.TableName == item).ToList();
                if (!string.IsNullOrEmpty(identity))
                {
                    table.Columns.Where(ex => ex.Name == identity).FirstOrDefault().IsIdentity = true;
                }
                if (!string.IsNullOrEmpty(primaryName))
                {
                    table.Columns.Where(ex => ex.Name == primaryName).FirstOrDefault().IsPrimaryKey = true;
                }

                DicTable.Add(item, table);
            }
            return DicTable;
        }

        #endregion
    }
}
